# coding=gbk

from DataAccess.blp_client import query_blp_data
from DataAccess.DBConnFactory import DBConnFactory
from Error.ExtendError import *
from Misc.xlDate import read_xl_date
from Misc.OpenExcel import open_xlsheet
from Misc.Utils import *

from string import Template


OVERSEA_FUND='SC028'


def check_existence(sql_text, cursor):
	cursor.execute(sql_text)
	r = cursor.fetchone()
	if r and len(r) > 0:    #already exist
		return (True, r[0])
	else:
		return (False, None)
		
	
def import_pnote(pkedb_conn,trade_record,portfolio_id):
	cursor = pkedb_conn.cursor()
	
	isin=trade_record[1]+' Corp'		
	trade_date=trade_record[0]
	trade_type=trade_record[2]
	security_type='PNOTE'
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]

	#insert trade
	sql_text = '''insert into transaction
				(trade_date,ticker,trade_type,portfolio_id,security_type,amount,cost_price,currency) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8)'''
	cursor.execute(sql_text, 
			(trade_date,isin,trade_type.upper(),portfolio_id,security_type,amount,cost_price,currency))
	pkedb_conn.commit()
	
	if check_existence(Template('''select * from pnote_info where isin_code='${ISIN}' ''').substitute(ISIN=isin), cursor)[0]:
		return
	
	
	#currency checking with blp
	r_blp=query_blp_data([isin], ['CRNCY','UNDERLYING_SECURITY_DES'])
	if len(r_blp[isin])==0:
		raise BLPDataError(), "Failed to get data for " + isin
		
	pnote_curncy=r_blp[isin]['CRNCY'].upper()
	underlying_blp_ticker=r_blp[isin]['UNDERLYING_SECURITY_DES']
	
	r_blp=query_blp_data([underlying_blp_ticker], ['CRNCY'])
	if len(r_blp[underlying_blp_ticker])==0:
		raise BLPDataError(), "Failed to get data for " + underlying_blp_ticker
	underlying_currency=r_blp[underlying_blp_ticker]['CRNCY'].upper()	

	if pnote_curncy!=trade_record[5]:
		print 'warn, currency of PNOTE is not same as blp ', pnote_curncy
		return
	
	#underlying info
	underlying_ticker=blp_ticker_to_ch_ticker(underlying_blp_ticker)
	
	#update pnote_info table 
	sql_tpl = Template('''merge into pnote_info pi
					using (select '${ISIN}' isin, '${TICKER}' uly_tk, '${CURNCY}' curncy, '${UNDERLY_CURNCY}' uly_curncy from dual) t
					on (pi.isin_code=t.isin)
					when matched then update set 
						pi.underlying_ticker=t.uly_tk,
						pi.currency=t.curncy,
						pi.underlying_currency=t.uly_curncy
					when not matched then insert 
						values(t.isin,uly_tk,curncy,uly_curncy)''')
	sql_text=sql_tpl.substitute(ISIN=isin, 
								TICKER=underlying_ticker, 
								CURNCY=currency,
								UNDERLY_CURNCY=underlying_currency)	
	pkedb_conn.cursor().execute(sql_text)
	
	sql_tpl = Template('''merge into comm_security_static_info s
					using (select '${ISIN}' tk,"name" from comm_security_static_info
							where ticker='${ULY_TK}') t
					on (t.tk=s.ticker)
					when matched then update set 
						s."name"=t."name"
					when not matched then insert(ticker,"name")
						values(t.tk,t."name")''')
	pkedb_conn.cursor().execute(sql_tpl.substitute(ISIN=isin, ULY_TK=underlying_ticker))
	pkedb_conn.commit()
	
	
def import_futures(pkedb_conn,trade_record,portfolio_id):	
	cursor = pkedb_conn.cursor()

	ticker=trade_record[1]		
	trade_date=trade_record[0]
	trade_type=trade_record[2]
	security_type='FUTURES'
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]
	
	sql_text = '''insert into transaction
				(trade_date,ticker,trade_type,portfolio_id,security_type,amount,cost_price,currency) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8)'''
	pkedb_conn.cursor().execute(sql_text, 
				(trade_date,ticker,trade_type.upper(),portfolio_id,security_type,amount,cost_price,currency))
	pkedb_conn.commit()
	
	
	if check_existence(Template('''select * from comm_security_static_info where ticker='${TK}' ''').substitute(TK=ticker), cursor)[0]:
		return
		
	#currency checking with blp
	#futures name and point value
	r_blp=query_blp_data([ticker], ['SHORT NAME','FUT_VAL_PT','CRNCY','EXCH_CODE'])
	if len(r_blp[ticker])==0:
		raise BLPDataError(), "Failed to get data for " + ticker
	
	#print ticker
	#print r_blp
	futures_name=r_blp[ticker]['SHORT NAME']
	point_value=r_blp[ticker]['FUT_VAL_PT']
	curr_blp=r_blp[ticker]['CRNCY'].upper()
	market=r_blp[ticker]['EXCH_CODE']
	
	if curr_blp!=trade_record[5]:
		print 'warn, currency of Futures is not same as blp ', curr_blp
		return
		

	#update futures info table 		
	sql_tpl = Template('''merge into comm_security_static_info s
						using (select '${TICKER}' tk, '${NAME}' nm from dual) t
							on (t.tk=s.ticker)
						when matched then update 
							set s."name"=t.nm
						when not matched then insert(ticker,"name") 
							values (t.tk,t.nm)''')
	sql_text=sql_tpl.substitute(TICKER=ticker, NAME=futures_name)
	pkedb_conn.cursor().execute(sql_text)
	
	sql_tpl = Template('''merge into comm_misc_static_info s 
						using (select '${TICKER}' tk, '${FIELD}' f, '${VALUE}' v from dual) t
							on (t.tk=s.ticker and t.f=s.field)
						when matched then update set 
							s."value"=t.v
						when not matched then insert (ticker,field,"value") values  
							(t.tk,t.f,t.v)''')
	sql_text=sql_tpl.substitute(TICKER=ticker, 
								FIELD='FUT_VAL_PT',
								VALUE=point_value)
	pkedb_conn.cursor().execute(sql_text)
	pkedb_conn.commit()			

		
def import_comm_stock(pkedb_conn,trade_record,portfolio_id):
	cursor = pkedb_conn.cursor()

	ticker=trade_record[1]
	trade_date=trade_record[0]
	trade_type=trade_record[2]
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]
	
	sys_ticker = ticker
	if blp_ticker_is_hk_ticker(ticker):
		sys_ticker = blp_ticker_to_hk_ticker(ticker)
		
	r = check_existence(Template('''select market from comm_security_static_info where ticker='${TK}' ''').substitute(TK=sys_ticker), cursor)	
	if r[0]:
		market = r[1] 
	else:
		#currency checking with blp
		r_blp=query_blp_data([ticker], ['CRNCY','EXCH_CODE','SHORT NAME'])
		if len(r_blp[ticker])==0:
			raise BLPDataError(), "Failed to get data for " + ticker

		curr_blp=r_blp[ticker]['CRNCY'].upper()
		market=r_blp[ticker]['EXCH_CODE']
		stock_name=r_blp[ticker]['SHORT NAME']
		
		if curr_blp!=trade_record[5]:
			print 'warn, currency of stock is not same as blp ', curr_blp
			return
				
		#update common stock info table			
		sql_tpl = Template('''merge into comm_security_static_info s
							using (select '${TICKER}' tk,'${NAME}' nm, '${MKT}' mkt from dual) t
								on (t.tk=s.ticker)
							when matched then update set
								s."name"=t.nm,
								s.market=t.mkt
							when not matched then insert values
								(t.tk,t.nm,t.mkt)''')
		sql_text=sql_tpl.substitute(TICKER=sys_ticker, NAME=stock_name, MKT=market)
		pkedb_conn.cursor().execute(sql_text)
		pkedb_conn.commit()		
		
	
	#insert trade	
	sql_text = '''insert into transaction 
				(trade_date,ticker,trade_type,portfolio_id,amount,cost_price,currency,market) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8)'''
	pkedb_conn.cursor().execute(sql_text, 
					(trade_date,sys_ticker,trade_type.upper(),portfolio_id,amount,cost_price,currency,market))
	pkedb_conn.commit()

	
def import_warrant(pkedb_conn,trade_record,portfolio_id):
	ticker=trade_record[1]
	trade_date=trade_record[0]
	trade_type=trade_record[2]
	amount=trade_record[3]
	cost_price=trade_record[4]
	currency=trade_record[5]
	sec_type=trade_record[-1]
	
	#insert trade	
	sql_text = '''insert into transaction 
				(trade_date,ticker,trade_type,portfolio_id,amount,cost_price,currency,security_type) 
				values(TO_DATE(:1,'yyyy-mm-dd'),:2,:3,:4,:5,:6,:7,:8)'''
	pkedb_conn.cursor().execute(sql_text, 
					(trade_date,ticker,trade_type.upper(),portfolio_id,amount,cost_price,currency,sec_type))
	pkedb_conn.commit()	
	
	
	
def read_trade(trade_date,file_path,sheet_index=0):

	portfolio_id = OVERSEA_FUND
	#portfolio_id = 'SCTest'
	
	ws_handle=open_xlsheet(file_path,sheet_index)
	if not ws_handle:
		raise IOError, ('Can not open xls file, ' + file_path)
	
	pkedb_conn = DBConnFactory().get_db_connection('PKEDB')
		
	num_rows=ws_handle.nrows
	#print "info,num_rows=", num_rows
	for row in range(2,num_rows):
	
		date=read_xl_date(ws_handle.cell_value(row, 8))
		ticker=ws_handle.cell_value(row, 9)
			
		if date==trade_date and ticker is not empty_cell:   
			direction=ws_handle.cell_value(row, 10)
			quantity=ws_handle.cell_value(row, 11)
			cost=ws_handle.cell_value(row, 12)
			currency=ws_handle.cell_value(row, 13)
			security_type=ws_handle.cell_value(row, 14)
					
			trade_record=[trade_date.strftime('%Y-%m-%d'),ticker,direction,quantity,cost,currency,security_type]
			print 'info,trade info: ', trade_record
					
			if security_type=='PNOTE':
				import_pnote(pkedb_conn,trade_record,portfolio_id)
			elif security_type=='FUTURES':
				import_futures(pkedb_conn,trade_record,portfolio_id)
			elif security_type=='WARRANT':
				import_warrant(pkedb_conn,trade_record,portfolio_id)
			else:
				import_comm_stock(pkedb_conn,trade_record,portfolio_id)
		else:
			continue
				
				
				
		
		
		
		
		
		
		
